package com.my.meitianbackup.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Formatter;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.c3p0.C3p0Plugin;

public class ExcelUtil {
	
	public static final String[] TITLE_FIELD = {"id","goodName","goodImg","goodDetailLink","type","sclick","price","sales","rate","bonus","wangwang","sellerId","shopName","platform","couponId","couponNum","couponRemain","couponInfo","couponStart","couponEnd","couponLink","popularizeLink"};
	public static final String[] EXPORT_TITLE = {"id","goodName","goodImg","goodDetailLink","type","sclick","price","sales","rate","bonus","wangwang","sellerId","shopName","platform","couponId","couponNum","couponRemain","couponInfo","couponPrice","couponStart","couponEnd","couponLink","popularizeLink"};
	public static final String FILE_PATH = "D:\\Documents\\Downloads\\精选优质商品清单(内含优惠券)-";
	public static final String FILE_PATH2 = "C:\\Users\\harvey.wang\\Downloads\\精选优质商品清单(内含优惠券)-";
	
	
	public static void readExcelWithTitle() throws Exception {
		Date date = new Date();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		String dateString = formatter.format(date);
		
		String fileName = FILE_PATH + dateString + ".xls";
		InputStream is = null;
		Workbook wb = null;
		
		try {
			is = new FileInputStream(fileName);

			wb = new HSSFWorkbook(is);;
			//List<Map<String, String>> result = new ArrayList<Map<String, String>>();// 对应excel文件

			Sheet sheet = wb.getSheetAt(0);

			List<String> titles = new ArrayList<String>();// 放置所有的标题
			int rowSize = sheet.getLastRowNum() + 1;
			
			for (int j = 0; j < rowSize; j++) {// 遍历行
				Row row = sheet.getRow(j);
				Record record = new Record();
				if (row == null) {// 略过空行
					continue;
				}
				int cellSize = row.getLastCellNum();// 行中有多少个单元格，也就是有多少列
				if (j == 0) {// 第一行是标题行
					
					for (int k = 0; k < cellSize; k++) {
						titles.add(TITLE_FIELD[k]);
					}
				} else {// 其他行是数据行
					//Map<String, String> rowMap = new HashMap<String, String>();// 对应一个数据行
					
					for (int k = 0; k < titles.size(); k++) {
						Cell cell = row.getCell(k);
						String key = titles.get(k);
						String value = null;
						if (cell != null) {
							value = cell.toString();
						}
						if(key.equals("couponInfo")) {
							String couponPrice = DBTools.getLastNumFromString(value);
							record.set("couponPrice",couponPrice);
						}
						record.set(key, value);
						//rowMap.put(key, value);
					}
					double price = Double.parseDouble(record.getStr("price"));
					double rate = Double.parseDouble(record.getStr("rate"))/100;
					double couponPrice = Double.parseDouble(record.getStr("couponPrice"));
					record.set("bonus", (price-couponPrice)*rate+"");
					
					record.set("finalPrice", (price-couponPrice));
					
					boolean isValid = checkData(record);
					if(!isValid)
						continue;
					
				
					try{
						Db.save("meitian", record);
					} catch(Exception e) {
						Db.update("meitian", record);
					}
					//result.add(rowMap);
					System.out.println(record.toString());
				}
			}
			//return result;
		} catch (FileNotFoundException e) {
			throw e;
		} finally {
			if (wb != null) {
				wb.close();
			}
			if (is != null) {
				is.close();
			}
		}
	}
	
	public static boolean checkData(Record record) {
		boolean isValid = true;
		if(Integer.parseInt(record.getStr("sales"))<200 || Double.parseDouble(record.getStr(("bonus")))<2) {
			isValid = false;
		}
		return isValid;
	}

}

